<?php


  /* functions for accessing Oracle database widgets */

####################################################################################
##                                 oci8_QueryObject                               ##
####################################################################################


class oci8_QueryObject {
   var $queryrecords; /* array containing records returned from query */
   var $tablename;
   var $querystring = "";
   var $whereclause;
   var $dbsystem = 'oracle';
   var $ogc_compliant = 0;
   var $dbconn;
   var $maxrecords = -1;
   var $result;
   var $adminsetup;
   var $adminsetuparray;
   var $showlabels = 1;
   var $viewcolumns = '';
   var $pk;
   var $debug = 0;
   var $show = 1;
   var $endline = "\n";
   var $rowclass0 = 'd0';
   var $rowclass1 = 'd1';
   var $lastserial = -1;
   var $outstring = '';
   var $alias_string = ''; # system specific key word for column and table aliases SQL SERVER/Oracle this is ''

   function performQuery() {


      if ($this->debug) {
         print("$this->querystring, <br>connection: $this->dbconn <br>");
      }


      if ( ($this->querystring <> "") && (isset($this->dbconn)) ) {


         $statement = oci_parse($this->dbconn,$this->querystring);
         $this->result = oci_execute($statement, OCI_DEFAULT);
         $this->queryrecords = array();
         if ($this->debug) {
            $nr = oci_num_rows($statement);
            $this->debugstring = "Returned $nr rows <br>";
         }
         $i = 0;
         if (!$this->result) {
            $e = oci_error($statement);
            $this->errorstring =  htmlentities($e['message']);
            return;
         } else {
            # only store a certain number of records, if not, simply store the result and exit
            while ($row = oci_fetch_assoc($statement) ) {
               array_push($this->queryrecords,$row);
               $i++;
               if ( ($i > $this->maxrecords) and ($this->maxrecords <> -1) ) {
                  print("Max records exceeded in query, result stored.");
               }
            }
         }
      }
   } /* end function performQuery() */

  function tableExists($tablename) {
     $this->querystring = "select count(*) as numtables from all_all_tables where table_name = '$tablename' ; ";
     $this->performQuery();
     if ( $this->getRecordValue(1,'numtables') > 0 ) {
        $te = 1;
     } else {
        $te = 0;
     }
     #error_log($this->querystring);

     return $te;
   }

   function getTables() {
      $this->querystring = "  select TABLE_NAME from all_all_tables ";
      $this->performQuery();
      $retarr = array();
      foreach($this->queryrecords as $thisrec) {
         array_push($retarr, $thisrec['TABLE_NAME']);
      }

      return $retarr;
   }
   
   function escapeString($thistext) {
      $etext = stripslashes($thistext); 
      return $etext;
   }

   function getColumns($tablename) {
      $this->querystring = "  select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME = '$tablename' ";
      $this->performQuery();
      $retarr = array();
      foreach($this->queryrecords as $thisrec) {
         array_push($retarr, $thisrec['COLUMN_NAME']);
      }

      return $retarr;
   }

  function cancel() {
  }

  function getRecordValue($recnumber, $colname) {
     if ( count($this->queryrecords) >= ($recnumber - 1)) {
        $thisrec = $this->queryrecords[($recnumber -1)];
        $thisval = $thisrec[$colname];
     } else {
        $thisval = "Error: Bad record ID";
     }
     return $thisval;
  }

  function array2tmpTable($values, $tablename, $colnames = array(), $valuetypes = array(), $forcelower = 1) {

     # expects an associative array columnname->value
     # check second line of array for column value types if they are not passed in
     if (count($values) == 0) {
        return;
     } else {
        if (count($colnames) == 0) {
           # use all columns if not instructed otherwise
           $rowkeys = array_keys($values);
           $colnames = array_keys($values[$rowkeys[0]]);
        }
        $cdel = '';
        $createsql = "create temp table $tablename (";
        $columnsql = " insert into $tablename (";
        $firstkey = current(array_keys($values));
        foreach($colnames as $thisname) {
           if ($this->debug) {
              error_log("Formatting $thisname - current key" . $firstkey . "<br>");
           }
           if (in_array($thisname, array_keys($valuetypes))) {
              $vtype = $valuetypes[$thisname];
           } else {
              # try to guess the type
              $thisval = $values[$firstkey][$thisname];

              if (is_numeric($thisval)) {
                 $vtype = 'float8';
              } elseif (strtotime($thisval)) {
                 $vtype = 'timestamp';
              } elseif (is_string($thisval)) {
                 $vtype = 'varchar(' . intval(2.0 * strlen($thisval) + 1) . ')';
              } else {
                 $vtype = 'varchar(' . intval(2.0 * strlen($thisval) + 1) . ')';
              }
              $valuetypes[$thisname] = $vtype;
           }

           $createsql .= $cdel . " \"$thisname\" $vtype ";
           $columnsql .= $cdel . " \"$thisname\" ";
           $cdel = ',';
        }
        $createsql .= ") ";
        $columnsql .= ") ";
        $te = $this->tableExists($tablename);
        if ( $te > 0 ) {
           # do nothing
        } else {
           $this->querystring = $createsql;
           if ( $this->debug) {
              error_log($createsql);
           }
           $this->performQuery();
        }

        foreach ($values as $thisrow) {
           $valsql = " values ( ";
           $vdel = '';
           foreach ($colnames as $thiscol) {
              if ($thisrow[$thiscol] == 'NULL') {
                 $valsql .= $vdel . $thisrow[$thiscol];
              } else {
                 $valsql .= $vdel . "'" . $thisrow[$thiscol] . "'";
              }
              $vdel = ',';
           }
           $valsql .= ")";

           $this->querystring = $columnsql . $valsql;
           if ($te > 0 and $this->debug) {
              # send the first line out to the error log
              error_log($this->querystring);
           }
           $this->performQuery();
        }
     }
  }



   function showDetail() {
      if (count($this->queryrecords) > 0) {
         foreach ($this->queryrecords as $thisrec) {
            $this->detailView($thisrec);
         }
      }
   } /* end showDetail() */


   function detailView($thisrec) {
      if (!isset($this->adminview)) {
         $this->getAdminSetupInfo($this->tablename,$this->viewcolumns);
         if ($this->debug) {
            print("Table: $this->tablename, Cols: $this->viewcolumns <br>");
         }
      }
      $formatinfo = $this->adminsetup;


      if ($formatinfo == "raw") {
         #print("Unformatted output");
         $formatinfo = array();
         $colkeys = array_keys($thisrec);
         $formatinfo["table info"] = array("pk"=>'');
         foreach ($colkeys as $thiskey) {
            $thisformat = array("type"=>1,"label"=>$thiskey,"visible"=>1);
            $formatinfo["column info"][$thiskey] = $thisformat;
         }
      }


      # get the value for this records pk, note: pk records do NOT get displayed
      $tableinfo = $formatinfo["table info"];
      $pkcol = $tableinfo["pk"];


      $pkvalue = $thisrec[$pkcol];
      $rowdesc = $formatinfo["column info"];
      #debug
      if ($this->debug) {
         print("Table: $this->tablename, pk column: $pkcol = $pkvalue<br>");
      }


      #
      # created above in favour of:


      if (strlen($this->viewcolumns) > 0) {
         $colkeys = array_keys($rowdesc);
      } else {
         $colkeys = array_keys($thisrec);
      }


      foreach ($colkeys as $colname) {
         # do the admin setup stuff
         $thisdesc = $rowdesc[$colname];
         $type = $thisdesc["type"];
         $params = $thisdesc["params"];
         $label = $thisdesc["label"];


         $value = $thisrec[$colname];


         if ($this->debug) {
            print("Handling column $colname, type: $type<br>");
         }


         $visible = $thisdesc["visible"];
         if ( ($this->showlabels) && ($visible) ) { print("<b>$label:</b>"); }
         if ( ($this->showlabels) && ($visible) ) {
            $this->printColumnValue($type,$params,$colname,$thisrec);
         }
         if ($visible) { print("<br>\n"); }


      } # end foreach (column)
   } /* end detailView */


   function showList() {


      # clear the output string
      $this->outstring = '';
      $numrecs = count($this->queryrecords);
      if ($this->debug) { print("number of records returned for $this->tablename: $numrecs<br>"); }

      if (!isset($this->adminview)) {
         $this->getAdminSetupInfo($this->tablename,$this->viewcolumns);
      }

      if (count($this->queryrecords) > 0) {
         $formatinfo = $this->adminsetup;
         if ($formatinfo == "raw") {
            $thisrec = $this->queryrecords[0];
            $formatinfo = array();
            $colkeys = array_keys($thisrec);
            $formatinfo["table info"] = array("pk"=>'');
            foreach ($colkeys as $thiskey) {
               $thisformat = array("type"=>1,"label"=>"$thiskey","visible"=>1);
               $formatinfo["column info"][$thiskey] = $thisformat;
               #print("$thiskey ");
            }
         }

         $rowdesc = $formatinfo["column info"];

         # commented out to force only showing columns that have been queried, in order of query
         #$rkeys = array_keys($rowdesc);
         # added next row
         $samplerow = $this->queryrecords[0];
         #modified next row
         $rkeys = array_keys($samplerow);
         $pk = $formatinfo["table info"]["pk"];

         if ($this->show) {
            print("\n<table>");
         }
         $this->outstring .= "$this->endline<table>";

         if ($this->showlabels) {
            if ($this->show) {
               print("\n<tr>\n");
            }
            $this->outstring .= "$this->endline<tr>$this->endline";
            foreach ($rkeys as $thiskey) {
               $label = $rowdesc[$thiskey]["label"];
               $visible = $rowdesc[$thiskey]["visible"];
               if ( ($visible) ) {
                  if ($this->show) {
                     print("<td valign=bottom><b>$label</b></td>");
                  }
                  $this->outstring .= "<td valign=bottom><b>$label</b></td>";
               }
            }
            if ($this->show) {
               print("\n</tr>\n");
            }
            $this->outstring .= "$this->endline</tr>$this->endline";
         }
         
         $n = 0; # row class number
         foreach ($this->queryrecords as $thisrec) {
            if ($this->show) {
               print("<tr>\n");
            }
            switch ($n) {
               case 0:
                  $cls = $this->rowclass0;
               break;
               
               case 1:
                  $cls = $this->rowclass1;
               break;
            }
            $this->outstring .= "<tr class='$cls'>$this->endline";
            $this->listView($thisrec);
            if ($this->show) {
               print("\n</tr>");
            }
            $this->outstring .= "$this->endline</tr>";
            
            $n += -1;
            $n = abs($n);
         }
         if ($this->show) {
            print("\n</table>");
         }
         $this->outstring .= "$this->endline</table>";
      }
   } /* end function showList() */


   function listView($thisrec) {


      if (!isset($this->adminview)) {
         $this->getAdminSetupInfo($this->tablename,$this->viewcolumns);
      }
      $formatinfo = $this->adminsetup;

      if ($formatinfo == "raw") {
         $formatinfo = array();
         $colkeys = array_keys($thisrec);
         $formatinfo["table info"] = array("pk"=>'');
         foreach ($colkeys as $thiskey) {
            $thisformat = array("type"=>1,"params"=>'',"label"=>$thiskey,"visible"=>1);
            $formatinfo["column info"][$thiskey] = $thisformat;
         }
      }

      # get the value for this records pk, note: pk records do NOT get displayed
      $tableinfo = $formatinfo["table info"];
      $pkcol = $tableinfo["pk"];

      $pkvalue = @$thisrec[$pkcol];
      $rowdesc = $formatinfo["column info"];
      #debug
      if ($this->debug) {

         print("Formatted row: pk = $pkcol => $pkvalue, visible = $visible, rowdesc = $rowdesc<br>");
      }

      if (strlen($this->viewcolumns) > 0) {
         $colkeys = array_keys($rowdesc);
      } else {
         $colkeys = array_keys($thisrec);
      }

      $rowout = "";
      foreach ($colkeys as $colname) {
         # do the admin setup stuff
         $thisdesc = $rowdesc[$colname];
         $type = $thisdesc["type"];
         $params = $thisdesc["params"];
         $label = $thisdesc["label"];
         $visible = $thisdesc['visible'];
         if ($formatinfo == "raw") {
            $visible = 1;
         }

         $value = $thisrec[$colname];

         if ($this->debug) {
            print("Handling column $colname, type: $type<br>");
         }
         if ($visible) {
            if ($this->show) {
               print("<td valign=top>\n");
            }
            $this->outstring .= "<td valign=top>$this->endline";
            $this->printColumnValue($type,$params,$colname,$thisrec);
            if ($this->show) {
               print("\n</td>\n");
            }
            $this->outstring .= "$this->endline</td>$this->endline";
         }
      } # end foreach (column)
   } /* end listView */




   function printColumnValue($type,$params,$cname,$thisrow) {


      $value = $thisrow[$cname];
      $pkvalue = @$thisrow[$this->pk];


      # for columns which don;t appear in the adminsetup table
      if (!($type > 0)) { $type = 1;}


      #print("PK: $pkvalue<br>");


      switch ($type) {


         case 0:
         # pk column, do nothing
         break;


         case 3:
         # multiple value lookup (aka multi select list)
            list($foreigntable,$pkcol,$listcols,$sortcol,$showlabels) = explode(":",$params);
            $pkvalue = $thisrow[$pkcol];
            $getlistsql = "select $listcols from $foreigntable where $pkcol = '$pkvalue'";
            #print("$getlistsql<br>");
            if ($sortcol <> "") { $getlistsql .= " order by ".$sortcol; }
            $listobject = new pgsql_QueryObject();
            $listobject->show = $this->show;
            $listobject->querystring = $getlistsql;
            $listobject->tablename = $foreigntable;
            $listobject->dbconn = $this->dbconn;
            $listobject->showlabels = $showlabels;
            $listobject->performQuery();
            $listobject->showList();
            $fvalue = '';
            $this->outstring .= $listobject->outstring;
         break;

         case 7: # percentage
            list($decimalplaces) = explode(":",$params);
            if ($decimalplaces == '') { $decimalplaces = 2; }
            $fvalue = number_format($value * 100.0,$decimalplaces);
            $fvalue = "$fvalue %";

         break;


         case 8: # scientific notation
            list($decimalplaces) = explode(":",$params);
            if ($decimalplaces == '') { $decimalplaces = 0; }
            $fvalue = sciFormat($value,$decimalplaces);
            $fvalue = "$fvalue";

         break;

         case 9: # number format
            list($decimalplaces) = explode(":",$params);
            if (strlen($decimalplaces) == 0) {
               $decimalplaces = 2;
            }
            $fvalue = number_format($value, $decimalplaces);
            $fvalue = "$fvalue";
         break;


         case 10: # currency format

            list($decimalplaces) = explode(":",$params);
            if ($decimalplaces == '') { $decimalplaces = 2; }
            $fvalue = number_format($value, $decimalplaces);
            $fvalue = "\$$fvalue";
         break;


         case 11: # 2-column map
         # multiple value lookup (aka multi select list)
            list($local1,$local2,$ftable,$foreign1,$foreign2,$listcols,$sortcol,$showlabels) = explode(":",$params);
            $l1 = $thisrow[$local1];
            $l2 = $thisrow[$local2];
            $getlistsql = "select $listcols from $ftable where ";
            $getlistsql .= "$foreign1 = '$l1' and $foreign2 = '$l2'";
            if ($sortcol <> "") { $getlistsql .= " order by ".$sortcol; }
            #print("$local1,$local2: $getlistsql<br>");
            $listobject = new pgsql_QueryObject();
            $listobject->show = $this->show;
            $listobject->querystring = $getlistsql;
            $listobject->tablename = $foreigntable;
            $listobject->dbconn = $this->dbconn;
            $listobject->showlabels = $showlabels;
            $listobject->performQuery();
            $listobject->showList();
            $fvalue = '';
            $this->outstring .= $listobject->outstring;
         break;


         case 12: # highlight min or max column value
         # multiple value lookup (aka multi select list)
            list($comptype,$compcol) = explode(":",$params);
            $compcols = explode(",",$compcol);
            $flagval = "unset";
            $flagcol = "";
            foreach ($compcols as $ccol) {
               $cval = $thisrow[$ccol];
               switch ($comptype) {
               case "min":
                  if ( ($cval < $flagval) || ($flagval == "unset") ) {
                     #print("$cval < $flagval <br>");
                     $flagval = $cval;
                     $flagcol = $ccol;
                  }
               break;

               case "max":
                  if ( ($cval > $flagval) || ($flagval == "unset") ) {
                     $flagval = $cval;
                     $flagcol = $ccol;
                  }
               break;
               }
            }
            if ($cname == $flagcol) {
               $fvalue = "<b>$value</b>";
            } else {
               $fvalue = "$value";
            }
         break;


         case 13: # print out min or max column value from list of columns in this row
         # multiple value lookup (aka multi select list)
            list($comptype,$nullval,$compcol) = explode(":",$params);
            $compcols = explode(",",$compcol);
            $flagval = "unset";
            $flagcol = "";
            foreach ($compcols as $ccol) {
               $cval = $thisrow[$ccol];
               switch ($comptype) {
               case "min":
                  if ( (($cval < $flagval) || ($flagval == "unset")) && ($cval <> $nullval) ) {
                     #print("$cval < $flagval <br>");
                     $flagval = $cval;
                     $flagcol = $ccol;
                  }
               break;


               case "max":
                  if ( ($cval > $flagval) || ($flagval == "unset") ) {
                     $flagval = $cval;
                     $flagcol = $ccol;
                  }
               break;
               }
            }
            $fvalue = "$flagval";


         break;


        case 14: # 2-column map
         # multiple value lookup (aka multi select list)
            list($local1,$local2,$ftable,$foreign1,$foreign2,$listcols,$sortcol,$showlabels) = explode(":",$params);
            $l1 = $local1;
            $l2 = $thisrow[$local2];
            $getlistsql = "select $listcols from $ftable where ";
            $getlistsql .= "$foreign1 = '$l1' and $foreign2 = '$l2'";
            if ($sortcol <> "") { $getlistsql .= " order by ".$sortcol; }
            #print("$local1,$local2: $getlistsql<br>");
            $listobject = new pgsql_QueryObject();
            $listobject->show = $this->show;
            $listobject->querystring = $getlistsql;
            $listobject->tablename = $foreigntable;
            $listobject->dbconn = $this->dbconn;
            $listobject->showlabels = $showlabels;
            $listobject->performQuery();
            $listobject->showList();
            $fvalue = '';
            $this->outstring .= $listobject->outstring;
         break;

         case 15: # edit link
            list($editpage,$extravars,$targetframe,$linktext,$urlextras) = explode(":",$params);

            $extralist = explode(',',$extravars);
            $extraurl = '';
            $udel = '';
            foreach ($extralist as $extrafield) {
               $thisfield = $thisrecord[$extrafield];
               $extraurl .= "$udel$extrafield=$thisfield";
               $udel = '&';
            }

            if (strlen($urlextras) > 0) {
               $extraurl .= "&$urlextras";
            }

            $fvalue = "<a href='$editpage?$extraurl&$pkcol=$pkvalue' target='$targetframe'>$linktext </a>";
         break;

         default:
         # text plain, numeric and text types (1,2)
            $fvalue = "$value";
         break;
      }

      if ($this->show) {
         print("$fvalue");
      }
      $this->outstring .= $fvalue;
   } /* end getColumnValue */


   function getAdminSetupInfo($tablename,$columns) {
      # retrieves admin setup info from table, or from existing array structure
      #      returns an associative array with the following:
      #           "table info" => array("pkcol"=>columname, "sortcol"=>columnname )
      #           "column info" => array(columname1=>array("type"=>displaytype,
      #                                                    "params"=>"param1:param2:param3",
      #                                                    "label"=>string )
      #      returns 0 if table is undefined
      # $dbconn - psql database connection id
      # $tablename - the name of the table to retrieve info for
      # $columns - a comma seperated list of columns to retrieve info for, if this is

      #            blank ALL columns will be retrieved


      $dbconn = $this->dbconn;
      #$exists = include('adminsetup.php'); /* creates array $adminsetuparray */


      #debug
      #print("get adminsetup stuff: $tablename, $adminsetuparray<br>");


      $askeys = array_keys($this->adminsetuparray);




      if ($this->debug) {
         print("All adminsetup entries: <br>");
         foreach(array_keys($this->adminsetuparray) as $thiskey) {
            print("&nbsp;&nbsp;&nbsp;$thiskey<br>");
         }
      }


      if (in_array($tablename,$askeys)) {
         $formatinfo = $this->adminsetuparray[$tablename];
         $tableinfo = $formatinfo["table info"];
         $columninfo = $formatinfo["column info"];
         if ($this->debug) {
            print("Columns: $columns <br>");
         }
         if (!($columns == '')) {
            foreach(explode(",",$columns) as $cname) {
               if ($this->debug) {
                  $allcols = implode(",",array_keys($columninfo));
                  print("Name: $cname in $allcols ??<br>");
               }
               if ( in_array($cname, array_keys($columninfo)) ) {
                  #print("$cname located.<br>");
                  $newcolumninfo[$cname] = $columninfo[$cname];
               }
            }
         } else {
            $newcolumninfo = $columninfo;
         }
         $formatinfo["table info"] = $tableinfo;
         $formatinfo["column info"] = $newcolumninfo;
         $this->pk = $tableinfo["pk"];
      } else {
         $formatinfo = "raw";
      }

      if ($this->debug) { print("$formatinfo<br>"); }
      $this->adminsetup = $formatinfo;
   }


    /* end function getAdminSetupInfo($dbconn,$tablename,$columns) */

} /* end oci8_QueryObject */



?>